package com.dao.qsl;

import com.bean.qsl.User;
import com.bean.qsl.YuYdInfo;
import com.bean.qsl.YyFwInfo;
import com.util.qsl.Dbutil;
import org.springframework.stereotype.Repository;

import javax.servlet.http.HttpServletRequest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class FuWuYd {
      /**
       * 服务预定
       * @return
       */
      public List<YuYdInfo> fwyd(HttpServletRequest request){
	  List<YuYdInfo> list=new ArrayList<>();
	  Map<String,Object> user = (Map<String,Object>)request.getSession().getAttribute("user");


	  Integer   vid = (Integer) user.get("vid");


	  Integer   aid = (Integer) user.get("aid");
//	  if(user==null){
//
//	        return list;
//	  }else {
//		  if (user.get("aid")!=null) {
//			  aid = (Integer) user.get("aid");
//		  }else{
//			  return  list;
//		  }
//	  }
	  Dbutil d=new Dbutil();
	  Connection c=null;

	  try {
	      c=d.getConnection();
	        String sql=null;
	        PreparedStatement pr=null;

	        String idtype =null;
	      if(aid==null){
		  sql="SELECT * from service ";
		  pr=c.prepareStatement(sql);
	      }else {

		  sql="SELECT * from service where xqmc=(SELECT vname FROM  village where vid=?) and fwbh not  in(SELECT fwbh from servicereservation where aid=? )";
		  pr=c.prepareStatement(sql);
		  pr.setInt(1,vid);
		  pr.setInt(2,aid);
	      }
	      ResultSet rs=pr.executeQuery();


		    while (rs.next()) {
			YuYdInfo y = new YuYdInfo();
			y.setId(rs.getInt("fwbh"));
			y.setFwmc(rs.getString("fwmc"));
			y.setKssj(rs.getString("kssj"));
			y.setJssj(rs.getString("jssj"));
			list.add(y);
		    }

	  }catch(Exception e){
	        e.printStackTrace();
	  }
	  return list;
      }

      /**
       * 判断是业主还是物业
       */
      public List<User> ifyzwy(HttpServletRequest request ){

	  List<User> list = new ArrayList();

	  Map<String,Object> user = (Map<String,Object>)request.getSession().getAttribute("user");

	  Integer   vid = (Integer) user.get("vid");

	  Integer   aid = (Integer) user.get("aid");

	  User u = new User();
	  if(aid==null){

	        System.out.println("登入人物业");

	        u.setName("物业");
	       list.add(u);
	        return  list ;
	  }else {
//	        User u = new User();
	        System.out.println("登入人业主");
	        u.setName("业主");
	        list.add(u);
	        return list ;
	  }
      }

      /**
       * 活动预定
       */
      public  List<YuYdInfo> hdyd(HttpServletRequest request){
	  List<YuYdInfo> list=new ArrayList<>();
	  Map<String,Object> user = (Map<String,Object>)request.getSession().getAttribute("user");

	  Integer   vid = (Integer) user.get("vid");
	  Integer   aid = (Integer) user.get("aid");

	  Dbutil d=new Dbutil();
	  Connection c=null;

	  try {
	        c=d.getConnection();
	        String sql=null;
	        PreparedStatement pr=null;
	        String idtype =null;
	        if(aid==null){
		    sql="SELECT cid,cname,startDate,endDate from community ";
		    pr=c.prepareStatement(sql);
	        }else {
		    sql="SELECT cid,cname,startDate,endDate from community where vid=? and cid  not in (SELECT cid from bsignup where aid=?)";
		    pr=c.prepareStatement(sql);

		    pr.setInt(1, vid);
		    pr.setInt(2, aid);

	        }


	        ResultSet rs=pr.executeQuery();

	        while (rs.next()){
		    YuYdInfo y=new YuYdInfo();

		    y.setId(rs.getInt("cid"));
		    y.setFwmc(rs.getString("cname"));
		    y.setKssj(rs.getString("startDate"));
		    y.setJssj(rs.getString("endDate"));
		    list.add(y);
	        }
	  }catch (Exception e){
	        e.printStackTrace();
	  }
	  return list;
      }

      /**
       * 添加服务预定信息
       */
      public Boolean addfwyd(Integer id , String ly, HttpServletRequest request){

	  Map<String,Object> user = (Map<String,Object>)request.getSession().getAttribute("user");
	  int  aid = (int) user.get("aid");
	  System.out.println("aid:"+aid);

	  Dbutil d=new Dbutil();
	  Connection c=null;
	  try {
	        c=d.getConnection();
	        String sql="INSERT into servicereservation VALUES(null,NOW(),?,?,?)";
	        PreparedStatement pr=c.prepareStatement(sql);
	        pr.setString(1,ly);
	        pr.setInt(2,id);
	        pr.setInt(3,aid);

	        int i=pr.executeUpdate();

	        if(i>0){
		    return true;
	        }else{
		    return false;
	        }
	  }catch(Exception e){
	        e.printStackTrace();
	  }
	  return false;

      }
      /**
       * 添加活动预定
       */
      public Boolean addhdyd(int id ,HttpServletRequest request){

	  Map<String,Object> user = (Map<String,Object>)request.getSession().getAttribute("user");
	  int  aid = (int) user.get("aid");

	  Dbutil d=new Dbutil();
	  Connection c=null;

	  try {
	        c=d.getConnection();
	        String sql="INSERT into bsignup VALUES(null,NOW(),?,?)";
	        PreparedStatement pr=c.prepareStatement(sql);
	        pr.setInt(1,aid);
	        pr.setInt(2,id);

	        int i=pr.executeUpdate();

	        if(i>0){

		    return xgbmrs(id);
	        }else{
		    return false;
	        }
	  }catch (Exception e){
	        e.printStackTrace();
	  }
	  return false;
      }

      /**
       * 预约成功活动报名人数+1
       */
      public  Boolean xgbmrs( int cid){
	  Dbutil d=new Dbutil();

	  Connection c=null;

	  try {
	        c=d.getConnection();
	        String sql="UPDATE community set bmrs=bmrs+1 where cid=?";
	        PreparedStatement pr=c.prepareStatement(sql);

	        pr.setInt(1,cid);

	        int i=pr.executeUpdate();

	        if(i>0){
		    return true;
	        }else{
		    return false;
	        }
	  }catch (Exception e){
	        e.printStackTrace();
	  }
	  return  false;
      }

      /**
       * 查看预约信息
       */
      public  List<YyFwInfo> ck(){
	  Dbutil d=new Dbutil();
	  Connection c=null;

	  List<YyFwInfo> list=new ArrayList<>();
	  try {
	        c=d.getConnection();

	        String sql="SELECT Hname, se.fwmc  from servicereservation s,household h  ,service se where s.aid=h.aid and s.fwbh=se.fwbh ";
	        PreparedStatement pr=c.prepareStatement(sql);
		ResultSet rs=pr.executeQuery();
		while (rs.next()){
		      YyFwInfo y=new YyFwInfo();
		      y.setYyname(rs.getString("Hname"));
		      y.setXxname(rs.getString("fwmc"));
		      list.add(y);
		}

	  }catch (Exception e){
	        e.printStackTrace();
	  }
	  return list;
      }

      /**
       * 查看预约信息
       */
      public  List<YyFwInfo> ckhd(){
	  Dbutil d=new Dbutil();
	  Connection c=null;

	  List<YyFwInfo> list=new ArrayList<>();
	  try {
	        c=d.getConnection();

	        String sql="SELECT  h.Hname  , c.cname from bsignup b,household h, community c where  b.aid=h.aid and c.cid=b.cid\n";
	        PreparedStatement pr=c.prepareStatement(sql);
	        ResultSet rs=pr.executeQuery();
	        while (rs.next()){
		    YyFwInfo y=new YyFwInfo();
		    y.setYyname(rs.getString("Hname"));
		    y.setXxname(rs.getString("cname"));
		    list.add(y);
	        }

	  }catch (Exception e){
	        e.printStackTrace();
	  }
	  return list;
      }
      /**
       * 业主查看已预约的服务
       */
      public  List<String>  selectyyfw(HttpServletRequest request){
	  Dbutil d=new Dbutil();

	  Connection c=null;

	  List<String> list=new ArrayList<String>();

	  Map<String,Object> user = (Map<String,Object>)request.getSession().getAttribute("user");
	  int  aid = (int) user.get("aid");
	  try {
	        c=d.getConnection();
	        String sql="SELECT fwmc from service where fwbh in (SELECT fwbh from servicereservation  WHERE aid =?)";

	        PreparedStatement pr=c.prepareStatement(sql);

	        pr.setInt(1,aid);

	        ResultSet rs=pr.executeQuery();

	        while (rs.next()){
		    list.add(rs.getString("fwmc"));
	        }

	  }catch (Exception e){
	        e.printStackTrace();
	  }
	  return list;
      }

      /**
       * 预定成功修改预定人数
       */
      public  Boolean  tjydrs(int id){
	  Dbutil d=new Dbutil();

	  Connection c=null;

	  try{
	        c=d.getConnection();
	        String sql="UPDATE service set ydrs=ydrs+1 where fwbh=?";

	        PreparedStatement pr=c.prepareStatement(sql);

	        pr.setInt(1,id);

	        int i=pr.executeUpdate();

	        if(i>0){
		    return true;
	        }else{
		    return false;
	        }



	  }catch (Exception e){
	        e.printStackTrace();
	  }
	  return false;
      }

      /**
       * 已预定的服务进行退订
       */

      public Boolean  tuiding(String yyname,HttpServletRequest request){
	  Dbutil d=new Dbutil();

	  Connection c=null;

	   Map<String,Object> user = (Map<String,Object>)request.getSession().getAttribute("user");
	  int  aid = (int) user.get("aid");

	  int fwbh=getfwbh(yyname);

	  System.out.println("服务编号："+fwbh);
	  try {
	        c=d.getConnection();
	        String sql="DELETE from servicereservation where fwbh=? and aid=?";
	        PreparedStatement pr=c.prepareStatement(sql);

	        pr.setInt(1,fwbh);
	        pr.setInt(2,aid);


	        int i=pr.executeUpdate();

	        if(i>0){

		    return  xgrs(fwbh);

	        }else{
		    return false;
	        }
	  }catch (Exception e){
	        e.printStackTrace();
	  }
	  return  false;
      }


      /**
       * 通过服务名称获取服务编号
       */
      public int getfwbh(String fwmc){
	  Dbutil d=new Dbutil();
	  Connection c=null;
	  int fwbh=0;
	  try {
	         c=d.getConnection();

	         String sql="SELECT fwbh from service where fwmc=?";
	         PreparedStatement pr=c.prepareStatement(sql);

	         pr.setString(1,fwmc);

	         ResultSet rs=pr.executeQuery();

	         if(rs.next()){
		     fwbh=rs.getInt("fwbh");

	         }
	  }catch (Exception e){
	        e.printStackTrace();
	  }
	  return  fwbh;
      }

      /**
       * 删除成功预约人数-1
       */
      public  Boolean  xgrs(int fwbh){
	  Dbutil d=new Dbutil();

	  Connection c=null;

	  try {
	        c=d.getConnection();

	        String sql="UPDATE service set ydrs=ydrs-1 where fwbh=?";

	        PreparedStatement pr=c.prepareStatement(sql);

	        pr.setInt(1,fwbh);

	        int i=pr.executeUpdate();

	        if(i>0){
		    return true;
	        }else{
		    return false;
	        }
	  }catch (Exception e){
	        e.printStackTrace();
	  }
	  return false;

      }

      /**
       * 查看活动预约的信息
       */
      public  ArrayList<String>  selecthdxx(HttpServletRequest request ){


	  Dbutil d=new Dbutil();
	  Connection c=null;

	  ArrayList<String> list=new ArrayList<>();

	  Map<String,Object> user = (Map<String,Object>)request.getSession().getAttribute("user");
	  int  aid = (int) user.get("aid");
	  try {
	        c=d.getConnection();

	        String sql="SELECT cname from  community where cid in(SELECT  cid from bsignup where aid=? )";

	        PreparedStatement pr=c.prepareStatement(sql);

	        pr.setInt(1,aid);

	        ResultSet rs = pr.executeQuery();

	        while (rs.next()) {
		    list.add(rs.getString("cname"));
	        }
	  }catch (Exception e){
	        e.printStackTrace();
	  }
	  return  list;
      }

      /**
       * 取消活动预定
       */

      public  Boolean quxhdyd(String yyname,HttpServletRequest request){

	        Dbutil d=new Dbutil();

	        Connection c=null;

	        Map<String,Object> user = (Map<String,Object>)request.getSession().getAttribute("user");
	        int  aid = (int) user.get("aid");

	        int cid=getcid(yyname);


	        try {
		    c=d.getConnection();
		    String sql="DELETE from bsignup where cid=? and aid=?";
		    PreparedStatement pr=c.prepareStatement(sql);

		    pr.setInt(1,cid);
		    pr.setInt(2,aid);


		    int i=pr.executeUpdate();

		    if(i>0){

			return  xghdrs(cid);

		    }else{
			return false;
		    }
	        }catch (Exception e){
		    e.printStackTrace();
	        }
	        return  false;
	  }

      /**
       * 通过活动名称获取活动编号
       */
      public int getcid(String cname){
	  Dbutil d=new Dbutil();
	  Connection c=null;
	  int cid=0;
	  try {
	        c=d.getConnection();

	        String sql="SELECT cid  from community where cname=?";
	        PreparedStatement pr=c.prepareStatement(sql);

	        pr.setString(1,cname);

	        ResultSet rs=pr.executeQuery();

	        if(rs.next()){
		    cid=rs.getInt("cid");

	        }
	  }catch (Exception e){
	        e.printStackTrace();
	  }
	  return  cid;
      }

      /**
       * 删除成功活动预约人数-1
       */
      public  Boolean  xghdrs(int cid){
	  Dbutil d=new Dbutil();

	  Connection c=null;

	  try {
	        c=d.getConnection();

	        String sql="UPDATE community set bmrs=bmrs-1 where cid=?";

	        PreparedStatement pr=c.prepareStatement(sql);

	        pr.setInt(1,cid);

	        int i=pr.executeUpdate();

	        if(i>0){
		    return true;
	        }else{
		    return false;
	        }
	  }catch (Exception e){
	        e.printStackTrace();
	  }
	  return false;

      }


}
